﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using SaludOcupacional.Exceptions;
using SaludOcupacional.Model;
using System.Collections.ObjectModel;

namespace SaludOcupacional.DataAccess
{
    public class LaboratorialDataAccess : IDisposable
    {
        private DatabaseManager dbManager;

        public void Dispose()
        {
            dbManager.Dispose();
        }
        public LaboratorialDataAccess()
        {
            dbManager = new DatabaseManager();
        }

        public Laboratorial BuscarAnalisisPorCi(int ci)
        {
            if (ci == 0)
            {
                throw new ArgumentNullException("ci");
            }
            // Create a Sql sentence
            string sqlsentence = "SELECT * FROM [DiagnosticoLaboratorial] WHERE ci_empleado = @Ci";

            dbManager.Command.CommandText = sqlsentence;
            dbManager.Command.Parameters.AddWithValue("@Ci", ci);

            Laboratorial resp = null;

            try
            {
                DataSet dataSet = dbManager.Query();

                if (dataSet.Tables[0].Rows.Count > 0)
                {
                    DataRow reader = dataSet.Tables[0].Rows[0];

                    resp = new Laboratorial
                    {
                        CodAnalisis = Convert.ToInt32(reader["cod_diagnostico"], System.Globalization.CultureInfo.InvariantCulture),
                        CiEmpleado = Convert.ToInt32(reader["ci_empleado"], System.Globalization.CultureInfo.InvariantCulture),
                        Widal = reader["widal"].ToString(),
                        Chagas = reader["chagas"].ToString(),
                        Diabetes = reader["diabetes"].ToString(),
                        Hepatitis = reader["hepatitis"].ToString(),
                        Vih = reader["vih"].ToString(),
                        Plomo = Convert.ToInt64(reader["plomo_sangre"], System.Globalization.CultureInfo.InvariantCulture),
                        Xileno = Convert.ToInt64(reader["xileno"], System.Globalization.CultureInfo.InvariantCulture),
                        Tolueno = Convert.ToInt64(reader["tolueno"], System.Globalization.CultureInfo.InvariantCulture),
                        Orina = reader["analisis_orina"].ToString(),
                        Hb = Convert.ToInt64(reader["hb"], System.Globalization.CultureInfo.InvariantCulture),
                        Hto = Convert.ToInt64(reader["hto"], System.Globalization.CultureInfo.InvariantCulture),
                        Glicemia = Convert.ToInt64(reader["glicemia"], System.Globalization.CultureInfo.InvariantCulture),
                        Colesterol = Convert.ToInt64(reader["colesterol"], System.Globalization.CultureInfo.InvariantCulture),
                        Trigliceridos = Convert.ToInt64(reader["trigliceridos"], System.Globalization.CultureInfo.InvariantCulture),
                        AcidoUrico = Convert.ToInt64(reader["acido_urico"], System.Globalization.CultureInfo.InvariantCulture),
                        Vdrl = reader["vdrl"].ToString(),
                        Creatina = Convert.ToInt64(reader["creatina"], System.Globalization.CultureInfo.InvariantCulture),
                        Observaciones = reader["observaciones"].ToString(),
                        Accion = reader["acciones_atomar"].ToString(),
                        Fecha = reader["fecha"].ToString()
                    };
                }
            }
            catch (Exception)
            {
                throw;
            }

            return resp;
        }

        public void RegistrarAnalisis(Laboratorial labo)
        {
            if (labo == null)
            {
                throw new LoginException("Invalid Input Parameters");
            }

            string sqlSentence = "INSERT INTO [dbo].[DiagnosticoLaboratorial]([ci_empleado],[widal],[chagas],[diabetes],[hepatitis],[vih],[plomo_sangre],[xileno],[tolueno],[analisis_orina],[hb],[hto],[glicemia],[colesterol],[trigliceridos],[acido_urico],[vdrl],[creatina],[observaciones],[acciones_atomar],[fecha])VALUES ( ";
            sqlSentence += "@Ci, @Widal, @Chagas, @Diabetes, @Hepatitis, @Vih, @Plomo, @Xileno, @Tolueno, @Orina, @Hb, @hto, @Glicemia, @Colesterol, @Trigliceridos, @Acidourico, @Vdrl, @Creatina, @Observaciones, @Acciones, @Fecha )";

            dbManager.Command.CommandText = sqlSentence;
            dbManager.Command.CommandType = System.Data.CommandType.Text;



            dbManager.Command.Parameters.AddWithValue("@Ci", labo.CiEmpleado);
            dbManager.Command.Parameters.AddWithValue("@Widal", labo.Widal);
            dbManager.Command.Parameters.AddWithValue("@Chagas", labo.Chagas);
            dbManager.Command.Parameters.AddWithValue("@Diabetes", labo.Diabetes);
            dbManager.Command.Parameters.AddWithValue("@Hepatitis", labo.Hepatitis);
            dbManager.Command.Parameters.AddWithValue("@Vih", labo.Vih);
            dbManager.Command.Parameters.AddWithValue("@Plomo", labo.Plomo);
            dbManager.Command.Parameters.AddWithValue("@Xileno", labo.Xileno);
            dbManager.Command.Parameters.AddWithValue("@Tolueno", labo.Tolueno);
            dbManager.Command.Parameters.AddWithValue("@Orina", labo.Orina);
            dbManager.Command.Parameters.AddWithValue("@Hb", labo.Hb);
            dbManager.Command.Parameters.AddWithValue("@hto", labo.Hto);
            dbManager.Command.Parameters.AddWithValue("@Glicemia", labo.Glicemia);
            dbManager.Command.Parameters.AddWithValue("@Colesterol", labo.Colesterol);
            dbManager.Command.Parameters.AddWithValue("@Trigliceridos", labo.Trigliceridos);
            dbManager.Command.Parameters.AddWithValue("@Acidourico", labo.AcidoUrico);
            dbManager.Command.Parameters.AddWithValue("@Vdrl", labo.Vdrl);
            dbManager.Command.Parameters.AddWithValue("@Creatina", labo.Creatina);
            dbManager.Command.Parameters.AddWithValue("@Observaciones", labo.Observaciones);
            dbManager.Command.Parameters.AddWithValue("@Acciones", labo.Accion);
            dbManager.Command.Parameters.AddWithValue("@Fecha", labo.Fecha);


            try
            {
                int value = dbManager.NonQuery();

                if (value < 1)
                {
                    throw new LoginException("The record wasn't registered");
                }
            }
            catch (Exception e)
            {
                throw new LoginException("Error creating a user" + e.Message, e);
            }
        }

        public void EditarAnalisis(Laboratorial labo)
        {
            if (labo == null)
            {
                throw new LoginException("The Analisis can´t be null");
            }

            string sqlSentence =
                "UPDATE [DiagnosticoLaboratorial]  SET [widal] = @Widal, [chagas] = @Chagas, [diabetes] = @Diabetes, [hepatitis] = @Hepatitis, ";
            sqlSentence = sqlSentence + "[vih] = @Vih, [plomo_sangre] = @Plomo, ";
            sqlSentence = sqlSentence + "[xileno] = @Xileno, [tolueno] = @Tolueno, ";
            sqlSentence = sqlSentence + "[analisis_orina] = @Orina, [hb] = @Hb, ";
            sqlSentence = sqlSentence + "[hto] = @Hto, [glicemia] = @Glicemia, ";
            sqlSentence = sqlSentence + "[colesterol] = @Colesterol, [trigliceridos] = @Trigliceridos, ";
            sqlSentence = sqlSentence + "[acido_urico] = @AcidoUrico, [vdrl] = @Vdrl, ";
            sqlSentence = sqlSentence + "[creatina] = @Creatina, [observaciones] = @Observaciones, ";
            sqlSentence = sqlSentence + "[acciones_atomar] = @Acciones, [fecha] = @Fecha ";
            sqlSentence = sqlSentence + " WHERE [ci_empleado] = @Ci";

            dbManager.Command.CommandText = sqlSentence;
            dbManager.Command.CommandType = System.Data.CommandType.Text;



            
            dbManager.Command.Parameters.AddWithValue("@Widal", labo.Widal);
            dbManager.Command.Parameters.AddWithValue("@Chagas", labo.Chagas);
            dbManager.Command.Parameters.AddWithValue("@Diabetes", labo.Diabetes);
            dbManager.Command.Parameters.AddWithValue("@Hepatitis", labo.Hepatitis);
            dbManager.Command.Parameters.AddWithValue("@Vih", labo.Vih);
            dbManager.Command.Parameters.AddWithValue("@Plomo", labo.Plomo);
            dbManager.Command.Parameters.AddWithValue("@Xileno", labo.Xileno);
            dbManager.Command.Parameters.AddWithValue("@Tolueno", labo.Tolueno);
            dbManager.Command.Parameters.AddWithValue("@Orina", labo.Orina);
            dbManager.Command.Parameters.AddWithValue("@Hb", labo.Hb);
            dbManager.Command.Parameters.AddWithValue("@Hto", labo.Hto);
            dbManager.Command.Parameters.AddWithValue("@Glicemia", labo.Glicemia);
            dbManager.Command.Parameters.AddWithValue("@Colesterol", labo.Colesterol);
            dbManager.Command.Parameters.AddWithValue("@Trigliceridos", labo.Trigliceridos);
            dbManager.Command.Parameters.AddWithValue("@AcidoUrico", labo.AcidoUrico);
            dbManager.Command.Parameters.AddWithValue("@Vdrl", labo.Vdrl);
            dbManager.Command.Parameters.AddWithValue("@Creatina", labo.Creatina);
            dbManager.Command.Parameters.AddWithValue("@Observaciones", labo.Observaciones);
            dbManager.Command.Parameters.AddWithValue("@Acciones", labo.Accion);
            dbManager.Command.Parameters.AddWithValue("@Fecha", labo.Fecha);
            dbManager.Command.Parameters.AddWithValue("@Ci", labo.CiEmpleado);


            try
            {
                int value = dbManager.NonQuery();
                if (value < 1)
                {
                    throw new LoginException("The change wasn't registered");
                }

            }
            catch (Exception e)
            {
                throw new LoginException("Error edit profile", e);
            }
        }
    }
}
